package com.wechat.order.util.excelUtils;

import com.google.common.collect.Maps;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.jdom2.Document;
import org.jdom2.Element;
import org.jdom2.input.SAXBuilder;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * Created by zzy on 2017/12/31.
 */
@Slf4j
public class ExcelReport {

    /**
     * 导出excel模板
     */

    private  HttpServletRequest request;

    private HttpServletResponse response;

    public ExcelReport(HttpServletRequest request, HttpServletResponse response) {
        this.request = request;
        this.response = response;
    }

    /**
     * 下载excel
     */
    public void exportTemplateToExcel(){
       // String path=request.getServletContext().getRealPath("/xml/student.xml");
        String path=this.getClass().getClassLoader().getResource("xml/student.xml").getPath();
        File file=new File(path);
        SAXBuilder builder=new SAXBuilder();
        Map map= Maps.newHashMap();
        map.put(null,null);
         try {
            //解析xml
            Document parse= builder.build(file);
            //创建excel
            HSSFWorkbook workbook=new HSSFWorkbook();
            HSSFSheet sheet=workbook.createSheet("Sheet0");

            //获取xml文件根节点
            Element root=parse.getRootElement();
            //获取模板名称
            String templateName=root.getAttribute("name").getValue();

            int rownum=0;
            int columnnum=0;
            //设置列宽
            Element colgroup=root.getChild("colgroup");
            ExcelProSetUtil.setColumnWidth(sheet,colgroup);
            //设置标题
            Element title=root.getChild("title");
            ExcelProSetUtil.setSheetTitle(workbook,sheet,title);
            rownum++;
            //设置表头
            Element thead=root.getChild("thead");
            ExcelProSetUtil.setSheetThead(sheet,thead);
            rownum++;
            //设置数据区域属性
            Element tbody=root.getChild("tbody");
            ExcelProSetUtil.setSheetTbody(workbook,sheet,tbody);

            String sFileName = templateName + ".xls";
            String userAgent = request.getHeader("User-Agent").toUpperCase();
            if(userAgent.contains("MSIE") || userAgent.contains("TRIDENT") || userAgent.contains("EDGE")){
                sFileName = URLEncoder.encode(sFileName,"UTF-8");
                sFileName = sFileName.replace("+", "%20");
            } else {
                sFileName = new String(sFileName.getBytes(), "ISO-8859-1");
            }

            //生成excel导入模板
            response.setHeader("Content-Disposition",String.format("attachment; filename=\"%s\"", sFileName));
            response.setHeader("Connection", "close");
            response.setHeader("Content-Type", "application/vnd.ms-excel");

            workbook.write(response.getOutputStream());

        } catch (Exception e) {
            e.printStackTrace();
        }
        System.out.println(path);
    }
    /**
     * 读取excel
     */
    public static List<List<String>> readExcel(InputStream fs){
        List<List<String>> resultList=new ArrayList<>();
        try {
            //创建excel文件
            HSSFWorkbook workbook=new HSSFWorkbook(fs);
            //获取工作表
            //HSSFSheet sheet=workbook.getSheet("Sheet0");
            HSSFSheet sheet=workbook.getSheetAt(0);
            int firstRowNum=0;
            int lastRowNum=sheet.getLastRowNum();

            for (int i=0;i<lastRowNum;i++){
                HSSFRow row=sheet.getRow(i);
                List<String> rowList=new ArrayList<>();
                for (int j=0;j<row.getLastCellNum();j++){
                    HSSFCell cell=row.getCell(j);
                    int cellType=cell.getCellType();
                    String cellValue=null;
/*                    Field[] fields=Cell.class.getFields();
                    for (Field field:fields) {
                        if(field.getName().contains("CELL_TYPE")&&String.valueOf(field.get(cell)).equals(cellType)){

                        }
                    } 期待通过反射简化代码*/
                    if(cellType==Cell.CELL_TYPE_STRING){
                        cellValue=cell.getStringCellValue();
                    }else if(cellType==Cell.CELL_TYPE_NUMERIC){
                        cellValue=String.valueOf(cell.getNumericCellValue());
                    }else if(cellType==Cell.CELL_TYPE_BOOLEAN){
                        cellValue=String.valueOf(cell.getBooleanCellValue());
                    }
                    rowList.add(cellValue);
                    System.out.print(cellValue+" ");
                }
                resultList.add(rowList);
                System.out.println();
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return resultList;
    }
}
